from db.mysql import mysql

async def get_company_count(keyword=None, l2_id=None):
    """
    通过名称关键词或者二级行业id查询企业数量
    """
    query1 = """
    SELECT COUNT(*) AS total 
    FROM company 
    WHERE company.name LIKE %s;
    """

    query2 = """
    SELECT COUNT(*) AS total 
    FROM company 
    WHERE company.l2_id = %s;
    """

    # 选择查询条件
    if l2_id:
        # 根据l2_id查数量
        result = await mysql.fetch_one(query2, (l2_id,))
    elif keyword:
        # l2_id 优先
        result = await mysql.fetch_one(query1, (f"%{keyword}%",))
    else:
        # 处理无查询条件的情况
        return {'total': 0}
    
    return result

async def get_company_byname(keyword, page=1, page_size=20):
    """
    通过名称关键词获取企业列表
    """
    offset = (page - 1) * page_size
    query = """
        SELECT 
            c1.id,
            c1.name,
            c1.address,
            c1.phone,
            COALESCE(GROUP_CONCAT(tag.name), '') AS tags
        FROM company c1
        LEFT JOIN company_tag_mapping m1 ON c1.id = m1.company_id
        LEFT JOIN tag ON m1.tag_id = tag.id
        WHERE c1.name LIKE %s
        GROUP BY 
            c1.id, 
            c1.name,
            c1.address,
            c1.phone
        ORDER BY c1.id
        LIMIT %s OFFSET %s;
    """
    params = (f"%{keyword}%", page_size, offset)
    result = await mysql.fetch_all_ori(query, params)

    return result

async def get_company_byl2id(l2id, page=1, page_size=20):
    """
    通过二级行业id获取企业列表
    """
    offset = (page - 1) * page_size
    query = """
        SELECT 
            c1.id,
            c1.name,
            c1.address,
            c1.phone,
            COALESCE(GROUP_CONCAT(tag.name), '') AS tags
        FROM company c1
        LEFT JOIN company_tag_mapping m1 ON c1.id = m1.company_id
        LEFT JOIN tag ON m1.tag_id = tag.id
        WHERE c1.l2_id = %s
        GROUP BY
            c1.id,
            c1.name,
            c1.address,
            c1.phone
        ORDER BY c1.id
        LIMIT %s OFFSET %s;
    """
    params = (l2id, page_size, offset)
    result = await mysql.fetch_all_ori(query, params)

    return result